Formula's
- Sum
- Product
- Average
- IF
- Days360
- Date
- Ceiling
- Floor
- Round
- Round up
- Round down
- Power
- Count
- CountA
- Count
- Sum if
- Maximum
- Minimum
- Hlookup for single worksheet
- Vlookup for single worksheet
- Hlookup & Vlookup for multiple Sheet
- Index
- Show formula
- Or
- Not
- Iferror
- Lookup
- Rate
- Ppmt
- Ipmt
- Networkdays
- Roundup
- Rounddown
- Mode
- Median
- Received
- Log
- Dollar
- AverageA
- Averageif
- Averageifs
- Mina
- Maxa
- Sumifs
- Large
- Pv
- Fv
- Coupdays
- Coupnum
- Datedif
- Daverage
- Dproduct
- Dmin
- Dmax
- Dcount
- Dsum
- Dget
- Now
- Today
- Weekday
- SQRT
- Mod
- Upper
- Lower
- Left
- Right
- Exact
- Len
- Match
- Roman
- Proper
- Pmt
- Concatenate
- IF Function & Nested IF Statements
- And & Or Functions
- Match and find record by VLOOKUP And HLOOKUP
- ISPMT
- SLN
- Effect
- CUMPRINC
- CUMIPMT
- EoMonth
- EDATE
- WORKDAYS
- DDB
- DB
- Approximate Match with Vlookup
- Exact match with Vlookup
- Nesting Lookup Function
- Choose Function
- Rank & Rank Average
- Rand & Rand Between
- Mod with Conditional Formatting
- Transpose
- Find & mid
- ISERR,
- OFFSET
- IIR
- NPV
- MONTH
- HOURS
- MINUTE
- NETWORKDAYS.INSTL
- Transpose with Vlookup
- ISNUM
- ISERROR
- IFERROR
- SUBSTITUTE
- INDIRECT
- SEARCH
- TRIM
- INDEX WITH SUM,COUNT,AVERAGE
Create Statements According to Function
MIS Report
- Profit & Loss a/c statement
- Balance sheet
- Worker contribution statement
- Cash flow statement
- Debtor’s & Creditors statement
- Inventory statement
- BRS Statement
- Fixed Assets & fund statement
- Payroll Report
- P.F & E.S.I Report
Working with the Web and External Data
- Inserting a Hyperlink
- Importing Data from an Access Database or Text File
- Importing Data from the Web and Other Sources
- Working with Existing Data Connections
WORKING WITH CHARTS
- Creating a Chart with the 2D or 3D
- Create Column Chart, Pie Chart
- Create Pateros Charts
- Create Speed- O-Meter Chart
- Moving a Chart One to another Sheet
- Resizing a Chart, Changing a Chart Type
- Editing Chart Text, Modifying Chart Options
- Formatting Category & Value Axis Data
- Formatting a Data Series
- Changing a Chart’s Source Data
Formats, Conditional Formatting & Sorting
- Cell Format.
- Data Bars, Color Scales & Icon Sets.
- Conditional formatting with formulas
- Conditional formatting with Form Controls.
- Sorting basic.
- Sorting based on Customized List.
- Customized Sorting (Advanced).
FORMATTING NUMBERS
- Using Conditional Formatting
- Removing Conditional Formatting
- Format As table
- Create our Styles
Data Management:
- Data Validation and dealing with Invalid Data.
- Group and Outline Data
- Data
- Data Consolidation.
- Data text to column.
- Custom Views
DATA ANALYSIS TOOLS
- Tracing Formula Precedents
- Tracing Cell Dependents
- Error Checking
- Creating a Pivot Table
- Rearranging a Pivot Table
- Filtering Pivot Table Data
- Creating a Pivot Chart
SUMMARIZING DATA
- Adding Subtotals to a List
- Nesting Subtotals
- Applying Advance Filters
- Adding Group & Outline Criteria to Ranges
- Using Data Validation
- Text to Column
WHAT –IF DATA ANALYSIS
- Using Goal Seek
- Using Data Table
- Creating & Displaying Scenarios
WORKING WITH LISTS
- Sorting Records in a List
- Using AutoFilter in a List
- Filter & Advance Filter
WORKGROUP COLLABORATIONS
- Locking/Unlocking Cells in a worksheet
- Protecting a Worksheet
- Protecting a Workbook
- Creating a Shared Workbook
- Tracking Changes to a Workbook
- Accepting & Rejecting Changes to a Workbook
- Password Protecting a Workbook
- Merging Workbooks
WORKGROUP COLLABORATION
- Web Page Preview
- Converting Worksheets into Web Pages
- Inserting Hyperlinks
- Viewing & Editing Comments
CUSTOMIZING EXCEL
- Creating a Macro with Condition
- Running a Macro, Editing a Macro
- Adding a menu to the Menu Bar
- Adding a Commands to a Menu
- Adding Buttons to a Toolbar
Print Settings
- Page Break
- Print Area
- Print Titles
- Background
Special Operation
- Link with One sheet,Another Worksheet , One Workbooks,Multiple Workbooks
- Find record by drop down list according To condition
- Dash Board
|
Understanding Databases
- Starting and Opening an Existing Database
- Moving Around in Access
- Understanding Datasheet View & Design View
- Using the Mouse Pointer to Navigate
- Using the Keyboard to Navigate
Creating Tables
- Creating a Database
- Creating a Table Using the Wizard
- Creating and Modifying a Table
- Adding Fields to Tables
- Adding and Editing Records
- Printing Tables
- Moving and Deleting Fields
- Deleting Records
Working with Tables
- Formatting a Table
- Modifying Field Properties
- Sorting Records in a Table
- Finding Records in a Table
- Using Filters with a Table
- Establishing Relationships Between Tables
- Creating Subdatasheets
- Importing Records From an External Source
Creating and Using Queries
- Creating and Running a Query
- Specifying Criteria in a Query
- Using Comparison Operators
- Creating a Calculated Field
- Creating a Multiple-Table Query
- Printing a Query
Designing a Form
- Creating a Form Using Auto Form
- Creating a Form Using the Form Wizard
- Adding Controls to a Form
- Modifying Control Properties
- Resizing and Moving Controls
- Entering Records into a Form
- Creating Calculated Controls
Designing a Report
- Creating a Report Using Auto Report
- Creating a Report Using Report Wizard
- Adding a Control to a Report
- Formatting a Report
- Resizing and Moving Controls
- Creating Calculated Controls
- Previewing and Printing
Automating Tasks
- Creating an Auto Keys Macro
- Using Controls to Run a Macro
- Assigning a Macro to an Event
- Assigning a Macro to a Condition
- Testing and Debugging a Macro
I - SQL Overview
- Relational database concepts, specific products
- SQL syntax rules
- Data definition, data manipulation, and data control statements
- Getting acquainted with the course database and editor
II - SQL SELECT statements
- Clauses
- The SELECT clause: columns and aliases, where expressions, order by expressions how null values behave
III - SQL Functions and Expressions
- Eliminating duplicates with DISTINCT arithmetic expressions
- Replacing null values
- Numeric operations, including rounding
- Date and time functions
- Nested table expressions
- Case logic
- Other expressions in specific dbms products
IV - SQL Updating
- The INSERT, UPDATE and DELETE statements
- Column constraints and defaults
- Referential integrity constraints
V - SQL Joins
- Inner joins with original and SQL 92 syntax
- Table aliases
- Left, right and full outer joins, Inner joins
- Self-joins
VI - SQL Sub queries and Unions
- Intersection with IN, and, Between
- Sub queries
- Difference with IS NULL and IS NOT NULL sub queries
- The purpose and usage of UNION and UNIONALL
VII - SQL Summarization
- The column functions MIN, MAX, AVG, SUM and COUNT, UPPER, LENGTH, LOWER
- The GROUP BY and HAVING clauses Grouping in a combination with joining
MODULE 1
What is VBA & HOW VBA WORKS WITH EXCEL
- Quick Review of Macros
- Introducing the Visual Basic Editor
- Uses of record Macros
- Understanding and creating modules
MODULE 2
PROGRAMMING CONCEPTS
- VBA Sub and Function Procedures
- How to create a message box
- Write a Program to update and retrieve information using Input Box
- Understanding and using Select Case statement
- How do I define a variables and Rules for defining a Variables Name and Type
- Creating And using Variables
- Working with range Objects
- How to save and Protect Modules
MODULE 3
Decision Makers
- If……Then……Else
- If……Then……ElseIf……If
MODULE 4
Other Kinds of Loops
- Working with Do While u. Loop Procedure
- Do…… Until Loop and Do…… Loop Until
- Do……While loop and Do…… Loop While
- For each…… Next
- For……Next
MODULE 5
Workbook Obects Create or Add Single and Multiple Workbooks
- Workbook Save and Save AS
- Open Single and Multiple Workbooks
- Close Specify and Multiple Workbooks
- Activate From one workbook to another Workbook
- Open Workbook from Specific Path
- Get Workbook Name and Paths
- Hide and Unhide for Single and Multiple Workbooks
- Protect and Unprotect Worksheets
MODULE 6
Worksheet Objects insert a single and Multiple Worksheets
- Delete Specific and multiple worksheets
- Get Count of Worksheets
- Select a Specific and all Worksheets
- Get All Worksheets Name
- Hide and Unhide For Single and Multiple Worksheets
- Rename for Single and Multiple Worksheets
- Protect and unprotect worksheets
- Sort and Move worksheets
- Calculate entire worksheet
- Using VBA and worksheet Function
MODULE 7
Cell objects insert Single and multiple Row , Column and Cells
- Delete Single and Multiple row, Column and Cells
- Get Range or Address of Cell and Selection
- Navigate from one Cell to another Cell
- Select specific Range, Cell, Rows and Column
- Types of Selection and Offset method
- Insert Function In cell
MODULE 8
Reading and Writing Arrays
- Defining Arrays
- Arrays as Outputs
- Arrays as inputs
- The Arrays as a Collection
- The Array as an Array
MODULE 9
Form Controls and User Forms (Create and Design an user Form)
- Working With User Forms & User Forms Events Like List Box, Combo Box, Option Buttons, Check Box, Text Box, Labels, Command Button, Toggle Button.
- How To Create Dynamic Dashboard On User Form With Different Controls
- How To Link Various User Form With Each Other To Create A Complete Interface Between User And System
MODULE 10
VBA Programming Functions
- Create a Sum Functions
- Create Multiply Function
- Create Count Function
- Extract Text & Number
- Proper Function
- Vlookup Function
- Square root Function
- Public or Private function
MODULE 11
Excel VBA Power Programming for VBA Macros
- Working with Dynamic Ranges. Protecting worksheets, Cells and Ranges. Working with Multiple Files. Opening &saving Files
- How to Analyze Data On multi Worksheets And Build Summary sheets
- How to Access the Windows File and Folder System to Open and Close Workbooks
- How to protect your code Against Errors
- How to use Excel And VBA to Create Basic Dash Boards
- How to create Your own custom Business Worksheet Function in VBA
- How to create Basic Report Generation Tools Using Excel VbA, Microsoft Word and PowerPoint
- How to use the Excel Visual Basic Macro record Excel Tasks in VBA And then Interpret the code
MODULE 12
Connection between Excel VBA & other platforms
- How To Establish Connection Between VBA And Power Presentation To Create Power Point Through VBA
- How To Establish Connection Between Excel Vba And Outlooks Through Vba
- How To Establish Connection Between Excel Vba And MS Word Through Vba
|
